package database;
import java.sql.*;
import java.util.ArrayList;

import model.Customer;
import oracle.jdbc.pool.OracleDataSource;

public class CustomerOperations 
{
	private Connection conn;
	private Statement stm;
	private ResultSet rst;
	private PreparedStatement prst;
	public CustomerOperations()
	{
		conn=openDB();
	}
	// open database connection
	public Connection openDB()
	{
		try 
		{
			OracleDataSource ods = new OracleDataSource();

			if ( test.Main.dataConn == "GaryTallaght"){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106491");
				ods.setPassword("db13May85");
			}
			else if ( test.Main.dataConn == "KeythTallaght"){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106271");
				ods.setPassword("db19Jan82");
			}
			else if ( test.Main.dataConn == "KeythHome"){
				ods.setURL("jdbc:oracle:thin:HR/localhost:1521:XE");
				ods.setUser("System");
				ods.setPassword("7481600kW");
			}
			else if ( test.Main.dataConn == "GaryHome"){
				ods.setURL("jdbc:oracle:thin:HR/SYSTEM@localhost:1521:XE");
				ods.setUser("SYSTEM");
				ods.setPassword("1nf1n1ty");
			}

			conn = ods.getConnection();
			System.out.println("connected cust.");
		} 
		catch (Exception e) 
		{
			System.out.print("Unable to load driver " + e);
			System.exit(1);
		}
		return conn;
	}
	// drop customer table from database
	public void dropCustomerTable() 
	{
		System.out.println("Checking for existing tables.");
		try
		{
			stm = conn.createStatement();

			try 
			{
				// Dropping a sequence
				String dropseq = "drop sequence custId_seq";
				stm = conn.prepareStatement(dropseq);
				stm.executeUpdate(dropseq);
			}
			catch(SQLSyntaxErrorException e)
				{
					
				}
				stm.execute("DROP TABLE Customer");
				System.out.println("Customer table dropped.");
			
		} 
		catch (SQLException ex) 
		{
			System.out.println("ERROR: " + ex.getMessage());
			//ex.printStackTrace();
		}
	}
	// create a customer table in database
	public void CreateCustomerTable() 
	{
		try 
		{
			stm = conn.createStatement();
			String create = "CREATE TABLE Customer "
					+ "(cust_id NUMBER PRIMARY KEY, cust_fname VARCHAR(40), cust_lname VARCHAR(40),cust_email VARCHAR(40),"
					+ "cust_phone NUMBER, cust_house VARCHAR(40), road_id number, FOREIGN KEY (road_id) REFERENCES Road)";

			stm.executeUpdate(create);
			System.out.println("customer table created");
			
			// Creating a sequence
			String createseq = "create sequence custId_seq increment by 1 start with 1 nocache";
			stm = conn.createStatement();
			stm.executeUpdate(createseq);
		} 
		catch (SQLException e) 
		{
			System.out.print("SQL Exception " + e);
			System.exit(1);
		}
	}
	// print all customers in table
	public void queryCustomer() 
	{
		try 
		{
			String queryString = "SELECT * FROM Customer";
			stm = conn.createStatement();
			rst = stm.executeQuery(queryString);
			System.out.println("Customer Table");
			while (rst.next()) 
			{
				System.out.println(rst.getInt(1) + " " + rst.getString(2)
						+ " " + rst.getString(3) + " "  + rst.getString(4) + " "
						+ rst.getInt(5) + " " + rst.getString(6) + " "
						+ rst.getInt(7));
			}
		} 
		catch (Exception e) 
		{
			System.out.println(e);
		}
	}
	// delete a customer from table
	public void deleteCustomer(int i) 
	{
		try 
		{
			String cmd = "DELETE FROM Customer WHERE cust_id =" + i;
			stm = conn.createStatement();
			stm.executeUpdate(cmd);
		} 
		catch (Exception e) 
		{
			System.out.println(e);
		}
	}
	// add a customer to the table
	public void addCustomer(Customer e) 
	{
		try 
		{
			String sql = "INSERT INTO Customer VALUES (custid_seq.nextVal,?,?,?,?,?,?)";

			prst = conn.prepareStatement(sql);
			prst.setString(1, e.getFname());
			prst.setString(2, e.getLname());
			prst.setString(3, e.getEmail());
			prst.setInt(4, e.getPhone());
			prst.setString(5, e.getHouseNum());
			prst.setInt(6, e.getRoadId());

			prst.executeUpdate();
		} 
		catch (Exception se) 
		{
			System.out.println(se);
			System.out.println("h");
		}
	}
	// return a result set of all customers in table
	public ResultSet getCustomer() 
	{
		try 
		{
			String queryString = "SELECT * FROM Customer ORDER BY cust_id";

			stm = conn.createStatement();
			rst = stm.executeQuery(queryString);
		} 
		catch (Exception e) 
		{
			System.out.println(e);
		}
		return rst;
	}
	// update the email of an customer
	public void updateCustomerEmail(int i,String e) 
	{
		try 
		{
			String sql = "UPDATE Customer SET cust_email='" + e + "' where cust_id="+ i;
			stm = conn.createStatement();
			stm.executeUpdate(sql);
		} 
		catch (Exception ae) 
		{
			System.out.println("Problem" + ae);
		}
	}
	// update the phone number of a customer		
	public void updateCustomerPhone(int i,int p) 
		{
			try 
			{
				String sql = "UPDATE Customer SET cust_phone=" + p + "where cust_id="+ i;
				stm = conn.createStatement();
				stm.executeUpdate(sql);
			} 
			catch (Exception e) 
			{
				System.out.println("Problem1" + e);
			}
		}
	
	// return an array list of all road names
	public ArrayList<String> getHouseNames(String en,int e) 
	{
		ArrayList<String> hse=new ArrayList<String>();
		try 
		{
			String queryString = "SELECT c.cust_house from road r,customer c where r.road_id=c.road_id and r.road_name='"+en+"' and r.est_id="+e+" order by c.cust_house";
			stm = conn.createStatement();
			rst = stm.executeQuery(queryString);
						
			while(rst.next())
				{
					hse.add(rst.getString(1));
				}
		} 
		catch (Exception ee) 
		{
			System.out.println("blah2");
			System.out.println(ee);
		}
		return hse;
	}
	
	//check address
	public boolean checkAddress(String h,int r)
	{
		boolean well=false;
		try 
		{
			String queryString = "SELECT cust_house,road_id from Customer";
			stm = conn.createStatement();
			rst = stm.executeQuery(queryString);
						
			while(rst.next())
				{
					if(rst.getString(1).equals(h) && rst.getInt(2)==r)
					{
						well=true;
					}
				}
		} 
		catch (Exception e) 
		{
			System.out.println(e);
		}
		return well;
	}
	
	//get current customer id
	public int getCurrentVal()
	{
		int a=-1;
		try
		{
			String queryString = "select CUSTID_SEQ.currval from users";
			stm = conn.createStatement();
			rst = stm.executeQuery(queryString);
						
			while(rst.next())
				{
					a=rst.getInt(1);
				}
		}
		catch(SQLException e)
		{
			
		}
		return a;
	}
	
	//return a single customer by address
	public Customer find(String h,int r)
	{
		Customer c = null;
		try 
		{
			String queryString = "SELECT * from customer where cust_house='"+h+"' and road_id="+r;
			stm = conn.createStatement();
			rst = stm.executeQuery(queryString);
			while(rst.next())
				{
					c=new Customer(rst.getInt(1),rst.getString(2),rst.getString(3),
							rst.getString(4),rst.getInt(5),rst.getString(6),rst.getInt(7));
				}
		} 
		catch (Exception e) 
		{
			System.out.println(e);
		}
		return c;
	}
	
	// get the estate id from its name
	public int getEstId(String name)
	{
		int a=-1;
		try 
		{
			String queryString = "SELECT est_name,est_id FROM Estates1 order by est_id";

			stm = conn.createStatement();
			rst = stm.executeQuery(queryString);
			while(rst.next())
			{
				if(rst.getString(1).equals(name))
				{
					a=rst.getInt(2);
				}
			}
		} 
		catch (SQLException e) 
		{
			System.out.println(e);
		}
		return a;
	}
	
	//get road id from the road name and estate id
	public int getRoadId(int e,String r)
	{
		int a = -1;
		try 
		{
			String queryString = "SELECT * from Road where est_id="+e+" and road_name='"+r+"'";
			stm = conn.createStatement();
			rst = stm.executeQuery(queryString);
			
			while(rst.next())
			{
				a=rst.getInt(1);
			}
		} 
		catch (Exception ee) 
		{
			System.out.println(ee);
		}
		return a;
	}
	
	// close the connection to database
	public void closeDB() 
	{
		try 
		{
			stm.close();
			rst.close();
			conn.close();
			System.out.print("Connection closed cust");
		} 
		catch (SQLException e) 
		{
			System.out.print("Could not close connection ");
			e.printStackTrace();
		}
	}
	
	public void closeDB2() 
	{
		try 
		{
			stm.close();
			//rst.close();
			conn.close();
			System.out.print("Connection closed cust");
		} 
		catch (SQLException e) 
		{
			System.out.print("Could not close connection ");
			e.printStackTrace();
		}
	}
}
